#  R 数据库

library("RMySQL");

# 创建一个数据库对象
mysqlClient <- dbConnect(MySQL(),
                         user="root",
                         password="xuyanzhao",
                         dbname = "haokan",
                         host="localhost"
                         )
# 获取表名称
# dbListTables( mysqlClient )
# 
# 查询表 先设置编码格式
dbSendQuery(mysqlClient,"SET NAMES gbk")

result <-dbSendQuery( mysqlClient,"SELECT * FROM `task`")

# 获取5行数据
data.frame <- fetch(result,n=5);
# print( data.frame )

dbClearResult( result )

# WHERE 条件
result <- dbSendQuery( mysqlClient,"SELECT * FROM `task` WHERE `link` = 'NONE'");

# n = -1 列出所有查询结果
data.frame <- fetch(result,n=-1)

# print( dataSELECT * FROM `task` WHERE `link` = "NONE".frame )

dbClearResult( result );

# 更新表中的行
dbSendQuery( mysqlClient,'UPDATE `task` SET `state` = 3 WHERE `link` = "NONE"');

# 插入数据
dbSendQuery( mysqlClient,'INSERT INTO `task` (`title`,`link`,`download`)VALUES("R program","https://www.r_learn/program","https://www.r_learn/download")')


# 创建mysql表

dbWriteTable(mysqlClient,"mtcars",mtcars[,],overwrite=TRUE);


      